--
-- @Author:      name
-- @DateTime:    2018-03-30 23:05:48
-- @Description: 数据库管理

local skynet = require "skynet"
local log = require "Logger"
local SqlHelper = require "SqlHelper"
local TbFactory = require "entitybase.EntityFactory"

local dbname = skynet.getenv("mysql_db")
local schema = {}

local Command = class("Command")

---------------------------------------------------------
-- Private
---------------------------------------------------------
function Command:ctor(message_dispatch)
	self.message_dispatch = message_dispatch
	self.tables = {}
	--加载表
	self.factory = TbFactory.new(self)
	self.mysql = nil
	self.redis = nil
	self.mysql_queue = nil -- 异步写入mysql
	self:register()
end

function Command:register()
	self.message_dispatch:registerSelf('start',handler(self,self.start))
	self.message_dispatch:registerSelf('load_tables',handler(self,self.loadTables))	
	self.message_dispatch:registerSelf('executeMySql',handler(self,self.executeMySql))
	self.message_dispatch:registerSelf('executeMySqlEx',handler(self,self.executeMySqlEx))
	self.message_dispatch:registerSelf('executeRedis',handler(self,self.executeRedis))
end

--获取表的主键
function Command:getPrimaryKey(tbname)
	local sql = "select k.column_name " ..
		"from information_schema.table_constraints t " ..
		"join information_schema.key_column_usage k " ..
		"using (constraint_name,table_schema,table_name) " ..
		"where t.constraint_type = 'PRIMARY KEY' " ..
		"and t.table_schema= '".. dbname .. "'" ..
		"and t.table_name = '" .. tbname .. "'"	
	local res = self:executeMySql(sql)
	
	if not next(res) then 		
		log.debug(tbname.."表没有主键______")
		return nil
	end
	return res[1]["column_name"]
end

--取表的所有字段
function Command:getFields(tbname)
	local sql = string.format("select column_name from information_schema.columns where table_schema = '%s' and table_name = '%s'", dbname, tbname)
	local rs = self:executeMySql(sql)
	local fields = {}
	for _, row in pairs(rs) do
		table.insert(fields, row["column_name"])
	end
	return fields
end

--取字段类型
function Command:getFieldType(tbname, field)
	local sql = string.format("select data_type from information_schema.columns where table_schema='%s' and table_name='%s' and column_name='%s'",
			dbname, tbname, field)
	local rs = self:executeMySql(sql)
	return rs[1]["data_type"]
end

--取数据库表与字段入到schema中
function Command:loadSchemaToRedis(tables)
	local table_list = {}
	if tables and next(tables) then 
		local tbname = ""
		for _, v in pairs(tables) do
			tbname = v.tbname or v.name
			table_list[tbname] = v	
		end
	else
		table_list = self.tables
	end
	for tbname,v in pairs(table_list) do 		
		schema[tbname] = {}
		schema[tbname]["fields"] = {}
		-- schema[tbname]["pk"] = self:getPrimaryKey(tbname)
		local fields = self:getFields(tbname)
		for _, field in pairs(fields) do
			local field_type = self:getFieldType(tbname, field)
			if field_type == "char"
				or field_type == "datetime"
			  	or field_type == "varchar"
			  	or field_type == "tinytext"
			  	or field_type == "text"
			  	or field_type == "mediumtext"
			  	or field_type == "longtext" then
				schema[tbname]["fields"][field] = "string"
			else
				schema[tbname]["fields"][field] = "number"
			end
		end
	end

end

--数据转换
function Command:converRecord(tbname, record)
	for k, v in pairs(record) do		
		if schema[tbname] and schema[tbname]["fields"][k] == "number" then
			record[k] = tonumber(v)
		end
	end
	return record
end

--表中的几个字段做为键在redis中显示
function Command:makeRedisKey(tbname, where_field_values)
    local rediskey = tbname
    for i, values in pairs(where_field_values) do
        for k, v in pairs(values) do 
            if type(v)=='boolean' then 
                if v then 
                    v = 1
                else
                    v = 0
                end
            end             
            rediskey = rediskey..":"..k .. ":" .. v
        end
    end
    return rediskey
end


--执行sql语句
function Command:executeMySql(sql, async)
	local res 
	if async then 
		res = skynet.call(self.mysql_queue, "lua", "add_sql", sql)
	else
		res = skynet.call(self.mysql, "lua", "execute", sql)
	end	
	local data = {}
	if not res or not next(res) then 
		--mysql取不到数据
		return data
	end
	for _, row in pairs(res) do
		table.insert(data, row)			
	end
	return data	
end

--执行redis命令
function Command:executeRedis(cmd, args, uid, fields)  
    table.concatList(args,fields or {})
    -- print("_____cmd, uid, table.unpack(args)__",cmd, uid, table.unpack(args))
    local result = skynet.call(self.redis, "lua", cmd, uid, table.unpack(args))    
    return self:redisToTable(result, fields)
end

-- table到mysql
function Command:executeMySqlEx(cmd, tbname, field_values, where_field_values, async)
	-- print("____sqlex___",cmd, tbname, field_values, where_field_values, async)
	local sql = nil
	local data = {}
	if cmd=="add" then 
		sql = SqlHelper.insertSql(tbname,field_values, where_field_values)
	elseif cmd=="delete" then 
		sql = SqlHelper.deleteSql(tbname, where_field_values)
	elseif cmd=="upadte" then 
		sql = SqlHelper.updateSql(tbname,field_values, where_field_values)
	elseif cmd=="get" then 
		sql = SqlHelper.selectSql(tbname,field_values, where_field_values)
	elseif cmd=="exists" then 
		sql = SqlHelper.isExistSql(tbname, where_field_values)
	elseif cmd=="modify" then 		
		sql = SqlHelper.modifySql(tbname,field_values, where_field_values)
	end	
	local res = self:executeMySql(sql,async)	
	if not res or not next(res) then 
		--mysql取不到数据
		return data
	end	
	for _, row in pairs(res) do
		table.insert(data, row)			
	end	
	if #data==1 then 
		return data[1]
	end
	return data	
end

--redis结果转为table
function Command:redisToTable(res, fields)
	if not res or type(res) ~= "table" then 
		return res
	end
	local data = {}
	if not fields then
		for i=1, #res, 2 do
			data[res[i]] = res[i+1]
		end
	else
		for i=1, #res do
			data[fields[i]] = t[i]
		end
	end
	return data
end



----------------------------------------------------------------------
--CMD
----------------------------------------------------------------------
function Command:start(tables, noUseRedis)
	log.debug("__databases_Command:start____")

	--mysql操作服务
	self.mysql = skynet.uniqueservice("mysql_service")
	skynet.call(self.mysql, "lua", "start")

	if not noUseRedis then 
		--redis操作服务
		self.redis = skynet.uniqueservice("redis_service")
		skynet.call(self.redis, "lua", "start")
	end
	--异步mysql操作
	self.mysql_queue = skynet.uniqueservice("sqlqueue_service")
	skynet.call(self.mysql_queue, "lua", "start")

	-- print("___V__",v,self.factory)
	if not tables then 
		return 
	end
	for _, v in pairs(tables) do
		self.tables[v.name] = v	
	end
	self:loadSchemaToRedis()
	--self:load_data_to_redis()
	for table_name,tb in pairs(self.tables) do 
		--表对象
		local entity = self.factory:get(table_name)
		entity:init(tb.pk,tb.key,tb.indexkey)	
	end	
end

--加载指定的表
function Command:loadTables(tables)
	if not tables then 
		return 
	end
	self:loadSchemaToRedis(tables)	
	for _, v in pairs(tables) do
		if not self.tables[v.name] then 
			self.tables[v.name] = v	
			-- print("___V__",v,self.factory)
			local entity = self.factory:get(v.name)
			entity:init(v)		
		else
			print("_表已加载过__",v.name)
		end
	end
end


----------------------------------------------------------------------
--数据库操作
----------------------------------------------------------------------

-- 从redis获取表数据，如果不存在，则从mysql加载
-- field_values为空，获取整行
function Command:get(obj, field_values, where_field_values)
	-- print("____get_____",field_values, where_field_values)
	local tbname = obj.tbname
	local result = nil
	local rediskey = nil
	if not obj.no_use_redis then 
		rediskey = self:makeRedisKey(tbname, where_field_values)		
		if field_values then
			result = self:executeRedis("hmget", { rediskey, table.unpack(field_values) })			
		else			
			result = self:executeRedis("hgetall", { rediskey })			
		end
	end
	-- print("_______redis________data____",result)
	-- redis没有数据返回，则从mysql加载
	if table.empty(result) then
		local sql = SqlHelper.selectSql(tbname,field_values, where_field_values)
		-- local res = skynet.call(self.mysql, "lua", "execute", sql)
		local data = self:executeMySql(sql)
		-- print("_______mysql________data____",data)
		for _, row in pairs(data) do
			if rediskey then 				
				self:executeRedis("hmset", {rediskey, row, true })
			end			
		end		
		if #data == 1 then
			result = data[1]
		else		
			result = data
		end
	end
	result = self:converRecord(tbname, result)

	return result
end

-- redis中增加一行记录，并同步到mysql
function Command:add(obj, field_values, where_field_values, nosync)
	local tbname = obj.tbname
	-- local sql = "insert into " .. tbname .. "(" .. columns .. ") values(" .. values .. ")"		
	local sql = SqlHelper.insertSql(tbname,field_values, where_field_values)
	log.debug(sql)
	local res = true
	res = self:executeMySql(sql, nosync)

	return res
end

-- redis中删除一行记录，并异步到mysql
function Command:delete(obj, where_field_values, nosync)

	local tbname = obj.tbname
	local rediskey = self:makeRedisKey(tbname, where_field_values)
	self:executeRedis("del", { rediskey })
 	local sql = SqlHelper.deleteSql(tbname, where_field_values)	
 	log.debug("_____delete_____sql_____",sql)
 	local res = true
	res = self:executeMySql(sql, nosync)
	return res

end

-- redis中更新一行记录，并异步到mysql
function Command:update(obj, field_values, where_field_values, nosync)
	-- print("___________1111______update_________",row)
	local tbname = obj.tbname
	local rediskey = self:makeRedisKey(tbname, where_field_values)
	local exists = self:executeRedis("exists", { rediskey })
	--存在才能update
	if exists and exists~="false" then		
		self:executeRedis("hmset", { rediskey, field_values })
	end		
	log.debug("_____update_____sql_____",sql)
	local sql = SqlHelper.updateSql(tbname, field_values, where_field_values)	
	local res = true	
	res = self:executeMySql(sql, nosync)

	return res
end

-- redis中删除一行记录，并异步到mysql
function Command:unload(obj, where_field_values, nosync)
	local tbname = obj.tbname
	local rediskey = self:makeRedisKey(tbname, where_field_values)
	self:executeRedis("del", { rediskey })
	return true
end

--增量加减
function Command:modify(obj, field_values, where_field_values, nosync)
	local tbname = obj.tbname
	local rediskey = self:makeRedisKey(tbname, where_field_values)
 	log.debug("_____modify___rediskey__row_____",rediskey, row)	
	for k, v in pairs(field_values) do		
		if v~=0 then 		
			--hincrby 一次只能操作一个属性
			self:executeRedis("hincrbyfloat", {rediskey, k, v })
		end
	end
	local sql = SqlHelper.modifySql(tbname, field_values, where_field_values)	
	log.debug("_____modify_____sql_____",sql)
	local res = true
	res = self:executeMySql(sql, nosync)
	return res
end

--是否存在
function Command:exists(obj, where_field_values)
	local tbname = obj.tbname
	local result = nil
	if not obj.no_use_redis then
		local rediskey = self:makeRedisKey(tbname, where_field_values)
		result = self:executeRedis("exists", { rediskey })
	end
	if result then 
		return true
	end
	local sql = SqlHelper.isExistSql(tbname, where_field_values)
	result = self:executeMySql(sql)
	return result
end

return Command